# -*- coding: utf-8 -*-
import uuid
from typing import List, Optional, Tuple

from sqlalchemy import and_, func, or_

from db.session import with_session
from db.models.files import AudioFile


@with_session
def db_get_audio_by_id(session, audio_id: uuid.UUID) -> Optional[AudioFile]:
    if isinstance(audio_id, str):
        audio_id = uuid.UUID(audio_id)
    return session.query(AudioFile).filter(AudioFile.id == audio_id).first()


@with_session
def db_get_audio_by_hash_name(session, hash_name: str) -> Optional[AudioFile]:
    return session.query(AudioFile).filter(
        AudioFile.hash_name == hash_name,
        AudioFile.is_delete.is_(False)
    ).first()


@with_session
def db_list_audio_files(
    session,
    *,
    name_like: str = None,
    status: str = None,
    created_by: int = None,
    offset: int = 0,
    limit: int = 50
) -> Tuple[List[AudioFile], int]:
    query = session.query(AudioFile).filter(AudioFile.is_delete.is_(False))

    if name_like:
        query = query.filter(AudioFile.name.ilike(f"%{name_like}%"))
    if status:
        query = query.filter(AudioFile.status == status)
    if created_by is not None:
        query = query.filter(AudioFile.created_by == created_by)

    total = query.count()
    items = query.order_by(AudioFile.updated_time.desc()).offset(offset).limit(limit).all()
    return items, total


@with_session
def db_list_audio_files_by_user_groups(
    session,
    user_group_ids: List[int],
    *,
    name_like: str = None,
    status: str = None,
    created_by: int = None,
    offset: int = 0,
    limit: int = 50
) -> Tuple[List[AudioFile], int]:
    """根据用户组ID列表获取音频文件"""
    if not user_group_ids:
        return [], 0
    
    query = session.query(AudioFile).filter(AudioFile.is_delete.is_(False))
    
    # 查询文件：要么group_ids为空（所有用户可访问），要么包含用户的组ID
    # 构建JSON查询条件：检查group_ids数组中是否包含用户的任何一个组ID
    json_conditions = []
    for group_id in user_group_ids:
        json_conditions.append(func.json_contains(AudioFile.group_ids, str(group_id)))
    
    query = query.filter(
        or_(
            AudioFile.group_ids == [],  # 空数组表示所有用户可访问
            *json_conditions  # 与用户组ID有交集
        )
    )

    if name_like:
        query = query.filter(AudioFile.name.ilike(f"%{name_like}%"))
    if status:
        query = query.filter(AudioFile.status == status)
    if created_by is not None:
        query = query.filter(AudioFile.created_by == created_by)

    total = query.count()
    items = query.order_by(AudioFile.updated_time.desc()).offset(offset).limit(limit).all()
    return items, total


@with_session
def db_create_audio_file(
    session,
    *,
    user_id: int,
    hash_name: str,
    name: str,
    location: str,
    file_type: str,
    file_size: float,
    status: str = None,
    company_name: str = None,
    group_name: str = None,
    content: str = None,
    summary: str = None,
    keywords: List[str] = None,
    comment: str = None,
    group_ids: List[int] = None
) -> AudioFile:
    audio = AudioFile(
        created_by=user_id,
        hash_name=hash_name,
        name=name,
        location=location,
        file_type=file_type,
        file_size=file_size,
        status=status,
        company_name=company_name,
        group_name=group_name,
        content=content,
        summary=summary,
        keywords=keywords or [],
        comment=comment,
        group_ids=group_ids or []
    )
    session.add(audio)
    session.commit()
    session.refresh(audio)
    return audio


@with_session
def db_update_audio_file(
    session,
    audio_id: uuid.UUID,
    *,
    name: str = None,
    location: str = None,
    file_type: str = None,
    file_size: float = None,
    status: str = None,
    company_name: str = None,
    group_name: str = None,
    content: str = None,
    summary: str = None,
    keywords: List[str] = None,
    comment: str = None,
    group_ids: List[int] = None
) -> Optional[AudioFile]:
    if isinstance(audio_id, str):
        audio_id = uuid.UUID(audio_id)
    audio = session.query(AudioFile).filter(AudioFile.id == audio_id).first()
    if not audio:
        return None

    if name is not None:
        audio.name = name
    if location is not None:
        audio.location = location
    if file_type is not None:
        audio.file_type = file_type
    if file_size is not None:
        audio.file_size = file_size
    if status is not None:
        audio.status = status
    if company_name is not None:
        audio.company_name = company_name
    if group_name is not None:
        audio.group_name = group_name
    if content is not None:
        audio.content = content
    if summary is not None:
        audio.summary = summary
    if keywords is not None:
        audio.keywords = keywords
    if comment is not None:
        audio.comment = comment
    if group_ids is not None:
        audio.group_ids = group_ids

    session.commit()
    session.refresh(audio)
    return audio


@with_session
def db_update_audio_file_delete_state(session, audio_id: uuid.UUID, is_delete: bool) -> bool:
    if isinstance(audio_id, str):
        audio_id = uuid.UUID(audio_id)
    audio = session.query(AudioFile).filter(AudioFile.id == audio_id).first()
    if not audio:
        return False
    audio.is_delete = is_delete
    session.commit()
    return True


